<%@page import="java.util.*, java.sql.*" %>

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Analytics - Disciplines</title>
</head>
<body>

<h1>List of applicants by degree discipline:</h1>

<%

	Connection conn = null;
	PreparedStatement stm = null;
	ResultSet result = null;
	
	try {
		Class.forName("org.postgresql.Driver");
		conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/Graduate_application_database", "postgres", "postgres");

		conn.setAutoCommit(false);
		stm = conn.prepareStatement("SELECT COUNT(distinct degrees.applicant), disciplines.discipline, disciplines.d_id " + 
									" FROM degrees, disciplines " + 
									" WHERE degrees.discipline = disciplines.d_id " + 
									" GROUP BY disciplines.discipline, disciplines.d_id ");
		result = stm.executeQuery();
		
		conn.commit();
		conn.setAutoCommit(true);
%>
		<table>
<%
		while (result.next())
		{%>
			<tr>
				<td><%= result.getString(2) %></td>
				<td><a href=application.jsp?discipline=<%=result.getInt(3) %>><%= result.getInt(1) %></a></td>
<%		}%>
		</table>
<%
		result.close();
		stm.close();
		conn.close();
	}
	catch (SQLException e)
	{
		throw new RuntimeException(e);
	}
    finally {
        if (result != null) {
            try {
                result.close();
            } 
            catch (SQLException e) { }
            result = null;
        }
        if (stm != null) {
            try {
                stm.close();
            } 
            catch (SQLException e) { }
            stm = null;
        }
        if (conn != null) {
            try {
                conn.close();
            } 
            catch (SQLException e) { }
            conn = null;
        }
    }
%>	

</body>
</html>